using System;
using System.Collections.Generic;
using System.Text;

namespace Rally_Dekstop
{
    class ExcelReader
    {
        Microsoft.Office.Interop.Excel.Application ExcelObj = null;
        Microsoft.Office.Interop.Excel.Workbook theWorkbook = null;
        Microsoft.Office.Interop.Excel.Sheets sheets=null;
        Microsoft.Office.Interop.Excel.Worksheet worksheet = null;
        public int RecordCount(string Filename)
        {
            int i = 1;
            
            sheets = theWorkbook.Worksheets;
            worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(1);

            string range = "A" + i;
            Microsoft.Office.Interop.Excel.Range MyRange = worksheet.get_Range(range, Type.Missing);
           
            while (MyRange.Value2 != null)
            {
                i++;
                range = "A"  + i;
                MyRange = worksheet.get_Range(range, Type.Missing);
                           
            }
            
            //actual number of records that would have to processed is i-2
            i=i-2;
            return i;      
        }

        public void FileOpen(string Filename)
        {
            ExcelObj = new Microsoft.Office.Interop.Excel.Application();
            if (ExcelObj == null)
            {

                //MessageBox.Show("ERROR: EXCEL couldn't be started!");

                System.Windows.Forms.Application.Exit();

            }
        
           theWorkbook = ExcelObj.Workbooks.Open(Filename, Type.Missing, Type.Missing, Type.Missing, Type.Missing,

           Type.Missing, Type.Missing, Type.Missing, Type.Missing,

           Type.Missing, Type.Missing, Type.Missing, Type.Missing,

           Type.Missing, Type.Missing);
        
        
        
        }
        public void FileClose()
        {
            theWorkbook.Close(Microsoft.Office.Interop.Excel.XlSaveAction.xlDoNotSaveChanges, Type.Missing, Type.Missing);
            
            ExcelObj.Quit();
            ExcelObj = null;
            
        }

        public string[] GetRecord(int index)
        {
            string range=null;;
            Microsoft.Office.Interop.Excel.Range MyRange;
            string[] newArray = null;
            newArray = new String[4];
            range = "A" + index;
            MyRange = worksheet.get_Range(range, Type.Missing);
            newArray[0] = Convert.ToString(MyRange.Value2); //Test case id

            range = "C" + index;
            MyRange = worksheet.get_Range(range, Type.Missing);
            newArray[1] = Convert.ToString(MyRange.Value2); // Verdict

            range = "D" + index;
            MyRange = worksheet.get_Range(range, Type.Missing);
            newArray[2] = Convert.ToString(MyRange.Value2); //Build

            range = "E" + index;
            MyRange = worksheet.get_Range(range, Type.Missing);
            newArray[3] = Convert.ToString(MyRange.Value2); //Notes

            return newArray;
                   
        }
  }
}
